-- File Name : sql_fulltext_mem_by_sqlid.sql
-- Purpose : Display the complete SQL statement and replace the bind variables with their actual values.
-- created : 2015/09/05
-- weixin、tel:18081072613
-- http://www.htz.pw
-- 20250811 modified by htz : Add literal SQL statement
-- 20250819 modified by htz : Modify the function replace_first_outside_quotes to handle the case of double quotes inside a literal
-- 20250821 modified by htz : Fetch bind values from v$sql_monitor.bind_xml filtered by SID and SQL_ID


set serveroutput on size 1000000

SET VERIFY OFF 
set linesize 200
set echo off
set pages 10000
undefine sqlid;
undefine sid;

DECLARE
  LVC_SQL_TEXT      VARCHAR2(32000);
  LVC_ORIG_SQL_TEXT VARCHAR2(32000);
  LVC_BIND          VARCHAR2(200);
  LVC_NAME          VARCHAR2(30);
  LN_BIND_COUNT     NUMBER := 0;
  
  FUNCTION replace_first_outside_quotes(
    p_text        IN VARCHAR2,
    p_pattern     IN VARCHAR2,
    p_replacement IN VARCHAR2
  ) RETURN VARCHAR2 IS
    v_pos        PLS_INTEGER := 1;
    v_len        PLS_INTEGER := NVL(LENGTH(p_text), 0);
    v_plen       PLS_INTEGER := NVL(LENGTH(p_pattern), 0);
    v_in_quote   BOOLEAN := FALSE;
    v_result     VARCHAR2(32767) := '';
    v_ch         CHAR(1);
    v_replaced   BOOLEAN := FALSE;
    v_next       CHAR(1);
  BEGIN
    IF v_len = 0 OR v_plen = 0 THEN
      RETURN p_text;
    END IF;

    WHILE v_pos <= v_len LOOP
      v_ch := SUBSTR(p_text, v_pos, 1);

      IF v_ch = '''' THEN
        IF v_in_quote THEN
          -- handle doubled quote inside a literal
          IF v_pos < v_len AND SUBSTR(p_text, v_pos + 1, 1) = '''' THEN
            v_result := v_result || '''''';
            v_pos := v_pos + 2;
            CONTINUE;
          ELSE
            v_in_quote := FALSE;
            v_result := v_result || v_ch;
            v_pos := v_pos + 1;
            CONTINUE;
          END IF;
        ELSE
          v_in_quote := TRUE;
          v_result := v_result || v_ch;
          v_pos := v_pos + 1;
          CONTINUE;
        END IF;
      END IF;

      IF NOT v_in_quote AND NOT v_replaced AND v_pos + v_plen - 1 <= v_len THEN
        IF UPPER(SUBSTR(p_text, v_pos, v_plen)) = UPPER(p_pattern) THEN
          -- avoid partial match like ':1' within ':10'
          v_next := CASE WHEN v_pos + v_plen <= v_len THEN SUBSTR(p_text, v_pos + v_plen, 1) ELSE NULL END;
          IF p_pattern LIKE ':%' THEN
            IF v_next IS NULL OR v_next NOT BETWEEN '0' AND '9' THEN
              v_result := v_result || p_replacement || SUBSTR(p_text, v_pos + v_plen);
              RETURN v_result;
            END IF;
          ELSE
            v_result := v_result || p_replacement || SUBSTR(p_text, v_pos + v_plen);
            RETURN v_result;
          END IF;
        END IF;
      END IF;

      v_result := v_result || v_ch;
      v_pos := v_pos + 1;
    END LOOP;

    RETURN v_result;
  END replace_first_outside_quotes;
BEGIN

  SELECT SQL_FULLTEXT
    INTO LVC_ORIG_SQL_TEXT
    FROM V$SQL
   WHERE SQL_ID = '&&sqlid'
     AND ROWNUM = 1;

  SELECT parsing_schema_name 
    INTO LVC_NAME 
    FROM v$sql 
   WHERE sql_id = '&&sqlid' 
     AND ROWNUM = 1;
  

  SELECT COUNT(*)
    INTO LN_BIND_COUNT
    FROM v$sql_monitor m,
         XMLTABLE('/binds/bind'
                  PASSING XMLTYPE(m.BINDS_XML)
                  COLUMNS name VARCHAR2(30) PATH '@name') x
   WHERE m.sql_id = '&&sqlid'
     AND m.sid = &&sid;
  

  IF LN_BIND_COUNT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Schema: ' || LVC_NAME);
    DBMS_OUTPUT.PUT_LINE(LVC_ORIG_SQL_TEXT);
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
    RETURN;
  END IF;
  

  LVC_SQL_TEXT := LVC_ORIG_SQL_TEXT;


  
  FOR R1 IN (
    SELECT x.name,
           x.position,
           x.dty,
           x.dtystr,
           x.value_string
      FROM v$sql_monitor m,
           XMLTABLE('/binds/bind'
                    PASSING XMLTYPE(m.BINDS_XML)
                    COLUMNS
                      name         VARCHAR2(30)    PATH '@name',
                      position     NUMBER          PATH '@pos',
                      dty          NUMBER          PATH '@dty',
                      dtystr       VARCHAR2(30)    PATH '@dtystr',
                      value_string VARCHAR2(4000)  PATH '.') x
     WHERE m.sql_id = '&&sqlid'
       AND m.sid = &&sid
     ORDER BY x.position
  ) LOOP

    IF R1.NAME LIKE ':SYS_B_%' THEN
      LVC_BIND := ':"'||substr(R1.NAME,2)||'"';
    ELSE
      LVC_BIND := R1.NAME;
    END IF;

        IF r1.VALUE_STRING IS NOT NULL AND TRIM(r1.VALUE_STRING) IS NOT NULL AND UPPER(TRIM(r1.VALUE_STRING)) <> 'NULL' THEN
      IF R1.DTYSTR IN ('NUMBER', 'BINARY_DOUBLE', 'BINARY_FLOAT') THEN
        LVC_SQL_TEXT := replace_first_outside_quotes(LVC_SQL_TEXT, LVC_BIND, R1.VALUE_STRING);
      ELSE
        LVC_SQL_TEXT := replace_first_outside_quotes(LVC_SQL_TEXT, LVC_BIND, ''''||R1.VALUE_STRING||'''');
      END IF;
    ELSE
       LVC_SQL_TEXT := replace_first_outside_quotes(LVC_SQL_TEXT, LVC_BIND, 'NULL');
    END IF;
  END LOOP;
  

  DBMS_OUTPUT.PUT_LINE(LVC_NAME);
  DBMS_OUTPUT.PUT_LINE(LVC_SQL_TEXT);
END;
/
undefine sqlid;
undefine sid;
SET VERIFY on
set echo on

